package com.gutai.admin.common.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

/**
 * 
 * 
 * 
 * <pre>
 * HISTORY
 * ****************************************************************************
 *  ID    DATE            PERSON            REASON
 *  1     2019年1月22日         la.01367604           Create
 * ****************************************************************************
 * </pre>
 * 
 * @author la.01367604
 * @version 1.0
 */
@Component
public class JdbcUtil implements InitializingBean {

    @Value("${gutai.mysql.datasource.url}")
    private String DB_URL = "jdbc:mysql://w.rdc.sae.sina.com.cn:3306/app_gutai?autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false";
    @Value("${gutai.mysql.datasource.username}")
    private String USER = "m3zon1ko4k";
    @Value("${gutai.mysql.datasource.password}")
    private String PASSWORD = "iykw1kkwmxxzm21klk1i431ziwhixixwl5h15w11";

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    private static JdbcUtil instance;

    private JdbcUtil() {}

    private Connection initConnect() {
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            // 打开链接
            System.out.println("连接数据库...");
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            System.out.println("连接数据库成功");
            return conn;
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConnect() {
        Connection conn = instance.initConnect();
        return conn;
    }

    public static <T> List<T> queryStatement(String sql, Class<T> clazz) throws Exception {
        Connection connect = getConnect();
        Statement stmt = null;
        try {
            stmt = connect.createStatement();
            // stmt.setQueryTimeout(100);
            ResultSet rs = stmt.executeQuery(sql);
            return transBeanRes(rs, clazz);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (connect != null) {
                connect.close();
            }
        }
    }

    public static int updateStatement(String sql) throws Exception {
        Connection connect = getConnect();
        Statement stmt = null;
        try {
            stmt = connect.createStatement();
            return stmt.executeUpdate(sql);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (connect != null) {
                connect.close();
            }
        }
    }

    /**
     * 将sql查询结果转换成实体类型的集合
     * 
     * @param rs
     * @param clazz
     * @return
     * @throws Exception
     * @author la.01367604-2019年1月22日
     */
    public static <T> List<T> transBeanRes(ResultSet rs, Class<T> clazz) throws Exception {
        // 结果集 中列的名称和类型的信息
        ResultSetMetaData rsm = rs.getMetaData();
        int colNumber = rsm.getColumnCount();
        List<T> list = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();

        while (rs.next()) {// 遍历每条记录
            T obj = clazz.newInstance();// 实例化对象
            // 取出每一个字段进行赋值
            for (int i = 1; i <= colNumber; i++) {
                String str = rs.getString(i);
                for (int j = 0; j < fields.length; j++) {// 匹配实体类中对应的属性
                    Field f = fields[j];
                    if (convertColmnName(f.getName()).equals(rsm.getColumnName(i))) {
                        f.setAccessible(true);
                        Object value = null;
                        if ("java.lang.Integer".equals(f.getType().getTypeName())
                                || "int".equals(f.getType().getTypeName())) {
                            value = Integer.parseInt(str);
                        } else if ("java.lang.Short".equals(f.getType().getTypeName())
                                || "short".equals(f.getType().getTypeName())) {
                            value = Short.parseShort(str);
                        } else if ("java.lang.Double".equals(f.getType().getTypeName())
                                || "java.lang.double".equals(f.getType().getTypeName())) {
                            value = Double.parseDouble(str);
                        } else {
                            value = f.getType().newInstance();
                            value = str;
                        }
                        f.set(obj, value);
                        break;
                    }
                }

            }
            list.add(obj);
        }
        return list;
    }

    /**
     * 转换驼峰式属性名成带下划线的属性名
     * 
     * @param name
     * @return
     * @author 01367604-2019年1月22日
     */
    private static String convertColmnName(String name) {
        if (StringUtils.isBlank(name)) {
            return "";
        }
        StringBuilder result = new StringBuilder();
        result.append(name.substring(0, 1).toLowerCase());
        for (int i = 1; i < name.length(); i++) {
            String s = name.substring(i, i + 1);
            String slc = s.toLowerCase();
            if (!s.equals(slc)) {
                result.append("_").append(slc);
            } else {
                result.append(s);
            }
        }
        return result.toString();
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        instance = this;
    }
}
